package com.lanyou.esb.repository;

import java.util.List;
import org.apache.shiro.authz.annotation.Logical;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;
import org.springframework.data.rest.core.annotation.RestResource;
import com.lanyou.esb.entity.Http;
/**
 * 
 * @author ly-zengzugen
 *
 */
public interface HttpRepository extends Repository<Http, String>, JpaSpecificationExecutor<Http> {

	
	@Query(nativeQuery = true,
			value = "SELECT tf.field_id fieldId, tf.field_name fieldName, ts.field_system_id fieldSystemId,"
					+ " ts.system_name systemName, h.if_http_id interfaceId, h.if_name interfaceName, h.if_type interfaceType, h.if_version interfaceVersion"
					+ " FROM t_cook_db_field tf LEFT JOIN t_cook_bu_field_system ts ON ts.field_id=tf.field_id LEFT JOIN t_esb_bu_if_http h"
					+ " ON ts.field_system_id=h.field_system_id AND h.is_enable = '1' WHERE ((h.is_public='1') OR"
					+ " EXISTS(SELECT u.field_system_id FROM t_cook_bu_user_system u WHERE ts.field_system_id=u.field_system_id"
					+ " AND u.user_id = :userId) OR"
					+ " EXISTS(SELECT vv.if_visible_system_id FROM t_esb_bu_if_visiblity vv WHERE"
					+ " vv.if_id=h.if_http_id AND EXISTS(SELECT uu2.field_system_id FROM t_cook_bu_user_system uu2 WHERE uu2.field_system_id=vv.if_visible_system_id"
					+ " AND uu2.user_id = :userId))) UNION"
					+ " SELECT tf.field_id fieldId, tf.field_name fieldName, ts.field_system_id fieldSystemId,"
					+ " ts.system_name systemName, f.if_ftp_id interfaceId, f.if_name interfaceName, f.if_type interfaceType, f.if_version interfaceVersion"
					+ " FROM t_cook_db_field tf LEFT JOIN t_cook_bu_field_system ts ON ts.field_id=tf.field_id LEFT JOIN t_esb_bu_if_ftp f"
					+ " ON ts.field_system_id=f.field_system_id AND f.is_enable = '1' WHERE ((f.IS_PUBLIC='1') OR"
					+ " EXISTS(SELECT u.field_system_id FROM t_cook_bu_user_system u WHERE ts.field_system_id=u.field_system_id"
					+ " AND u.user_id = :userId) OR"
					+ " EXISTS(SELECT vv.if_visible_system_id FROM t_esb_bu_if_visiblity vv WHERE"
					+ " vv.if_id=f.if_ftp_id AND EXISTS(SELECT uu2.field_system_id FROM t_cook_bu_user_system uu2 WHERE uu2.field_system_id=vv.if_visible_system_id"
					+ " AND uu2.user_id = :userId)))")
	List<Object[]> findVisibleInterfaces(@Param("userId") String userId);
	
	@Query(nativeQuery = true,
			value = "SELECT tf.field_id fieldId, tf.field_name fieldName, ts.field_system_id fieldSystemId,"
					+ " ts.system_name systemName, h.if_http_id interfaceId, h.if_name interfaceName, h.if_type interfaceType, h.if_version interfaceVersionZ"
					+ " FROM t_cook_db_field tf LEFT JOIN t_cook_bu_field_system ts ON ts.field_id=tf.field_id LEFT JOIN t_esb_bu_if_http h"
					+ " ON ts.field_system_id=h.field_system_id AND h.is_enable = '1'"
					+ " WHERE EXISTS(SELECT u.field_system_id FROM t_cook_bu_user_system u WHERE ts.field_system_id=u.field_system_id"
					+ " AND u.user_id = :userId) UNION"
					+ " SELECT tf.field_id fieldId, tf.field_name fieldName, ts.field_system_id fieldSystemId,"
					+ " ts.system_name systemName, f.if_ftp_id interfaceId, f.if_name interfaceName, f.if_type interfaceType, f.if_version interfaceVersion"
					+ " FROM t_cook_db_field tf LEFT JOIN t_cook_bu_field_system ts ON ts.field_id=tf.field_id LEFT JOIN t_esb_bu_if_ftp f"
					+ " ON ts.field_system_id=f.field_system_id AND f.is_enable = '1'"
					+ " WHERE EXISTS(SELECT u.field_system_id FROM t_cook_bu_user_system u WHERE ts.field_system_id=u.field_system_id"
					+ " AND u.user_id = :userId)")
	List<Object[]> findInterfacesForMsgByUser(@Param("userId") String userId);
	
	
	
	@Query(nativeQuery = true, value = "SELECT count(ins.interfaceId)"
			+ " FROM (SELECT th.if_http_id interfaceId, tfd.field_id fieldId, ts.field_system_id fieldSystemId, tfd.field_name fieldName,"
			+ " ts.system_name systemName, th.if_name interfaceName, th.if_code interfaceCode, th.if_type interfaceType," 
			+ " th.audit_status auditStatus, th.created_date createdDate, th.if_version interfaceVersion, th.if_desc interfaceDes, th.if_status interfaceStatus," 
			+ " (SELECT tb.BUNDLE_NAME FROM t_esb_bu_if_bundles_relation  thb, t_esb_bu_bundles tb WHERE (th.if_http_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.IS_DEFINED_BUNDLE_JAR = '1')"
							+ " OR (th.if_http_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.bundle_no = :bundleNo)) bundleName, th.bundle_type bundleType" 
			+ " FROM t_esb_bu_if_http th, t_cook_bu_field_system ts, t_cook_db_field tfd"
			+ " WHERE th.field_system_id = ts.field_system_id AND ts.field_id = tfd.field_id" 
			+ " AND EXISTS(SELECT tu.field_system_id FROM t_cook_bu_user_system tu WHERE th.field_system_id = tu.field_system_id AND tu.user_id = :userId)"
			+ " AND th.is_enable = '1'"
			+ " UNION" 
			+ " SELECT tf.if_ftp_id interfaceId, tfd.field_id fieldId, ts.field_system_id fieldSystemId, tfd.field_name fieldName, "
			+ " ts.system_name systemName, tf.if_name interfaceName, tf.if_code interfaceCode, tf.if_type interfaceType," 
			+ " tf.audit_status auditStatus, tf.created_date createDate, tf.if_version interfaceVersion, tf.if_desc interfaceDes, tf.if_status interfaceStatus," 
			+ " (SELECT tb.BUNDLE_NAME FROM t_esb_bu_if_bundles_relation  thb, t_esb_bu_bundles tb WHERE (tf.if_ftp_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.IS_DEFINED_BUNDLE_JAR = '1')"
							+ " OR (tf.if_ftp_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.bundle_no = :bundleNoFtp)) bundleName,'' bundleType" 
			+ " FROM t_esb_bu_if_ftp tf, t_cook_bu_field_system ts, t_cook_db_field tfd"
			+ " WHERE tf.field_system_id = ts.field_system_id AND ts.field_id = tfd.field_id" 
			+ " AND EXISTS(SELECT tu.field_system_id FROM t_cook_bu_user_system tu WHERE tf.field_system_id = tu.field_system_id AND tu.USER_ID = :userId)"
			+ " AND tf.is_enable = '1')"
			+ " ins" 
			+ " WHERE (''=:fieldId OR ins.fieldId = :fieldId)"
			+ " AND (''=:fieldSystemId OR ins.fieldSystemId = :fieldSystemId)"
			+ " AND (''=:interfaceName OR ins.interfaceName like :interfaceName)"
			+ " AND (''=:interfaceCode OR ins.interfaceCode like :interfaceCode)"
			+ " AND (''=:interfaceStatus OR ins.interfaceStatus = :interfaceStatus)"
			+ " AND (''=:auditStatus OR ins.auditStatus = :auditStatus)"
			+ " AND (''=:createdDate OR DATE_FORMAT(ins.createdDate, '%Y-%m-%d %H:%i:%S') BETWEEN DATE_FORMAT(:createdDate, '%Y-%m-%d %H:%i:%S')"
			+ " AND DATE_FORMAT(:endDate, '%Y-%m-%d %H:%i:%S'))")
	List<Object> countInterfacesByUser(@Param("bundleNo") String bundleNo,
						@Param("bundleNoFtp") String bundleNoFtp,
						@Param("userId") String userId,
						@Param("fieldId") String fieldId,
						@Param("fieldSystemId") String fieldSystemId,
						@Param("interfaceName") String interfaceName,
						@Param("interfaceCode") String interfaceCode,
						@Param("interfaceStatus") String interfaceStatus,
						@Param("auditStatus") String auditStatus,
						@Param("createdDate") String createdDate,
						@Param("endDate") String endDate);



	
	@Query(nativeQuery = true, value = "SELECT ins.interfaceId, ins.fieldId, ins.fieldSystemId, ins.fieldName, ins.systemName, ins.interfaceName, ins.interfaceCode, ins.interfaceType," 
							+ " ins.auditStatus, ins.createdDate, ins.interfaceVersion, ins.interfaceDes, ins.interfaceStatus, ins.bundleName, ins.bundleType"
							+ " FROM (SELECT th.if_http_id interfaceId, tfd.field_id fieldId, ts.field_system_id fieldSystemId, tfd.field_name fieldName,"
							+ " ts.system_name systemName, th.if_name interfaceName, th.if_code interfaceCode, th.if_type interfaceType," 
							+ " th.audit_status auditStatus, th.created_date createdDate, th.if_version interfaceVersion, th.if_desc interfaceDes, th.if_status interfaceStatus," 
							+ " (SELECT tb.BUNDLE_NAME FROM t_esb_bu_if_bundles_relation  thb, t_esb_bu_bundles tb WHERE (th.if_http_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.IS_DEFINED_BUNDLE_JAR = '1')"
							+ " OR (th.if_http_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.bundle_no =:bundleNo)) bundleName, th.bundle_type bundleType" 
							+ " FROM t_esb_bu_if_http th, t_cook_bu_field_system ts, t_cook_db_field tfd"
							+ " WHERE th.field_system_id = ts.field_system_id AND ts.field_id = tfd.field_id" 
							+ " AND EXISTS(SELECT tu.field_system_id FROM t_cook_bu_user_system tu WHERE th.field_system_id = tu.field_system_id AND tu.user_id = :userId)"
							+ " AND th.is_enable = '1'"
							+ " UNION" 
							+ " SELECT tf.if_ftp_id interfaceId, tfd.field_id fieldId, ts.field_system_id fieldSystemId, tfd.field_name fieldName, "
							+ " ts.system_name systemName, tf.if_name interfaceName, tf.if_code interfaceCode, tf.if_type interfaceType," 
							+ " tf.audit_status auditStatus, tf.created_date createDate, tf.if_version interfaceVersion, tf.if_desc interfaceDes, tf.if_status interfaceStatus," 
							+ " (SELECT tb.BUNDLE_NAME FROM t_esb_bu_if_bundles_relation  thb, t_esb_bu_bundles tb WHERE (tf.if_ftp_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.IS_DEFINED_BUNDLE_JAR = '1') "
											+ " OR (tf.if_ftp_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.bundle_no = :bundleNoFtp)) bundleName,'' bundleType"
							+ " FROM t_esb_bu_if_ftp tf, t_cook_bu_field_system ts, t_cook_db_field tfd"
							+ " WHERE tf.field_system_id = ts.field_system_id AND ts.field_id = tfd.field_id" 
							+ " AND EXISTS(SELECT tu.field_system_id FROM t_cook_bu_user_system tu WHERE tf.field_system_id = tu.field_system_id AND tu.USER_ID = :userId)"
							+ " AND tf.is_enable = '1')"
							+ " ins" 
							+ " WHERE (''=:fieldId OR ins.fieldId = :fieldId)"
							+ " AND (''=:fieldSystemId OR ins.fieldSystemId = :fieldSystemId)"
							+ " AND (''=:interfaceName OR ins.interfaceName like :interfaceName)"
							+ " AND (''=:interfaceCode OR ins.interfaceCode like :interfaceCode)"
							+ " AND (''=:interfaceStatus OR ins.interfaceStatus = :interfaceStatus)"
							+ " AND (''=:auditStatus OR ins.auditStatus = :auditStatus)"
							+ " AND (''=:createdDate OR DATE_FORMAT(ins.createdDate, '%Y-%m-%d %H:%i:%S') BETWEEN DATE_FORMAT(:createdDate, '%Y-%m-%d %H:%i:%S')"
							+ " AND DATE_FORMAT(:endDate, '%Y-%m-%d %H:%i:%S')) ORDER BY ins.createdDate DESC LIMIT :pageNumber,:pageSize")
	List<Object[]> findInterfacesByUser(@Param("bundleNo") String bundleNo,
										@Param("bundleNoFtp") String bundleNoFtp,
										@Param("userId") String userId,
										@Param("fieldId") String fieldId,
										@Param("fieldSystemId") String fieldSystemId,
										@Param("interfaceName") String interfaceName,
										@Param("interfaceCode") String interfaceCode,
										@Param("interfaceStatus") String interfaceStatus,
										@Param("auditStatus") String auditStatus,
										@Param("createdDate") String createdDate,
										@Param("endDate") String endDate,
										@Param("pageNumber") long pageNumber,
										@Param("pageSize") long pageSize);
	
	@Query(nativeQuery = true, value = "SELECT t2.IF_PRI_URL, t2.if_http_id, COUNT(t3.if_parameter_id)"
			+ " FROM t_esb_bu_if_http t2" 
			+ " LEFT JOIN t_esb_bu_if_parameter t3" 
			+ " ON t3.if_id = t2.if_http_id"
			+ " WHERE t2.IF_PRI_URL IN (SELECT t1.IF_PRI_URL" 
			+ " FROM t_esb_bu_if_http t1" 
			+ " GROUP BY t1.IF_PRI_URL"
			+ " HAVING COUNT(1) > 1)" 
			+ " AND t2.IF_PRI_URL IN (SELECT f.IF_PRI_URL"
			+ " FROM (SELECT f2.IF_PRI_URL,f2.if_http_id,COUNT(f3.if_parameter_id) AS param_cnt" 
			+ " FROM t_esb_bu_if_http f2"
			+ " LEFT JOIN t_esb_bu_if_parameter f3" 
			+ " ON f3.if_id = f2.if_http_id" 
			+ " WHERE f2.IF_PRI_URL IN"
			+ " (SELECT f1.IF_PRI_URL" 
			+ " FROM t_esb_bu_if_http f1" 
			+ " GROUP BY f1.IF_PRI_URL"
			+ " HAVING COUNT(1) > 1) " 
			+ " GROUP BY f2.IF_PRI_URL, f2.if_http_id"
			+ " HAVING COUNT(f3.if_parameter_id) > 0) f" 
			+ " GROUP BY f.IF_PRI_URL"
			+ " HAVING COUNT(f.IF_PRI_URL) > 1)" 
			+ " GROUP BY t2.IF_PRI_URL, t2.if_http_id"
			+ " HAVING COUNT(t3.if_parameter_id) > 0")
	List<Object[]> findAllResult();


	
	@Query(nativeQuery = true,
			value = "SELECT tb.systemName FROM("
					+ " SELECT ts.SYSTEM_NAME systemName FROM t_esb_bu_if_http th, t_cook_bu_field_system ts"
					+ " WHERE th.FIELD_SYSTEM_ID = ts.FIELD_SYSTEM_ID AND th.IS_ENABLE = '1' AND th.IF_NAME LIKE :name"
					+ " UNION" + " SELECT ts.SYSTEM_NAME FROM t_esb_bu_if_ftp tf, t_cook_bu_field_system ts"
					+ " WHERE tf.FIELD_SYSTEM_ID = ts.FIELD_SYSTEM_ID AND tf.IS_ENABLE = '1' AND tf.IF_NAME LIKE :name) tb"
					+ " GROUP BY tb.systemName")
	List<String> findVagueByName(@Param("name") String name);

	@RestResource(exported = false)
	Http findByCodeAndIsEnable(@Param("code") String code, @Param("isEnable") String isEnable);
	
	long countByCodeAndIsEnable(@Param("code") String code, @Param("isEnable") String isEnable);
	
	
	@Query(nativeQuery = true, value = "SELECT if_version version FROM t_esb_bu_if_http WHERE is_enable = '1' AND if_code = :code ORDER BY if_version DESC")
	List<String> findIfMaxVersionByCode(@Param("code") String code);
	
	@RestResource(exported = false)
	long countByNameAndIsEnable(@Param("name") String name, @Param("isEnable") String isEnable);
	
	
	long countByIsEnable(@Param("isEnable") String isEnable);
	
	long count();

	boolean exists(String id);

	@RestResource(exported = true)
	@RequiresPermissions(value={"businessServiceManagement:maintain"})
	<S extends Http> S save(S entity); // could expose REST API

	<S extends Http> List<S> save(Iterable<S> entities);

	@RestResource(exported = true)
	@RequiresPermissions(value={"businessServiceManagement:view", "businessServiceManagement:maintain"}, logical = Logical.OR)
	Http findOne(String id); // could expose REST API

	Http getOne(String id);

	// 以下三个接口只能选一个暴露为REST API

	@RestResource(exported = false)
	List<Http> findAll(); // could expose REST API

	@RestResource(exported = false)
	List<Http> findAll(Sort sort); // could expose REST API

	@RestResource(exported = true)
	@RequiresPermissions(value={"businessServiceManagement:view", "businessServiceManagement:maintain"}, logical = Logical.OR)
	Page<Http> findAll(Pageable pageable); // could expose REST API

	List<Http> findAll(Iterable<String> ids);

	// 以下两个接口，要么都暴露为REST API，要么都不暴露
	@RestResource(exported = true)
	@RequiresPermissions(value={"businessServiceManagement:maintain"})
	void delete(String id); // could expose REST API

	@RestResource(exported = true)
	@RequiresPermissions(value={"businessServiceManagement:maintain"})
	void delete(Http entity); // could expose REST API

	void delete(Iterable<? extends Http> entities);

	void deleteAll();

	void flush();

	<S extends Http> S saveAndFlush(S entity);

	void deleteInBatch(Iterable<Http> entities);

	void deleteAllInBatch();
	
	
	@Query(nativeQuery = true, value = "SELECT count(ins.interfaceId)"
			+ " FROM (SELECT th.if_http_id interfaceId, tfd.field_id fieldId, ts.field_system_id fieldSystemId, tfd.field_name fieldName,"
			+ " ts.system_name systemName, th.if_name interfaceName, th.if_code interfaceCode, th.if_type interfaceType," 
			+ " th.audit_status auditStatus, th.created_date createdDate, th.if_version interfaceVersion, th.if_desc interfaceDes, th.if_status interfaceStatus," 
			+ " (SELECT tb.BUNDLE_NAME FROM t_esb_bu_if_bundles_relation  thb, t_esb_bu_bundles tb WHERE (th.if_http_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.IS_DEFINED_BUNDLE_JAR = '1')"
							+ " OR (th.if_http_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.bundle_no = :bundleNo)) bundleName, th.bundle_type bundleType" 
			+ " FROM t_esb_bu_if_http th, t_cook_bu_field_system ts, t_cook_db_field tfd"
			+ " WHERE th.field_system_id = ts.field_system_id AND ts.field_id = tfd.field_id" 
			+ " AND EXISTS(SELECT tu.field_system_id FROM t_cook_bu_user_system tu WHERE th.field_system_id = tu.field_system_id AND tu.user_id = :userId)"
			+ " AND th.is_enable = '1')"
			+ " ins" 
			+ " WHERE (''=:fieldId OR ins.fieldId = :fieldId)"
			+ " AND (''=:fieldSystemId OR ins.fieldSystemId = :fieldSystemId)"
			+ " AND (''=:interfaceName OR ins.interfaceName like :interfaceName)"
			+ " AND (''=:interfaceCode OR ins.interfaceCode like :interfaceCode)"
			+ " AND (''=:interfaceStatus OR ins.interfaceStatus = :interfaceStatus)"
			+ " AND (''=:auditStatus OR ins.auditStatus = :auditStatus)"
			+ " AND (''=:createdDate OR DATE_FORMAT(ins.createdDate, '%Y-%m-%d %H:%i:%S') BETWEEN DATE_FORMAT(:createdDate, '%Y-%m-%d %H:%i:%S')"
			+ " AND DATE_FORMAT(:endDate, '%Y-%m-%d %H:%i:%S'))")
	List<Object> countHttpInterfacesByUser(@Param("bundleNo") String bundleNo,
						@Param("userId") String userId,
						@Param("fieldId") String fieldId,
						@Param("fieldSystemId") String fieldSystemId,
						@Param("interfaceName") String interfaceName,
						@Param("interfaceCode") String interfaceCode,
						@Param("interfaceStatus") String interfaceStatus,
						@Param("auditStatus") String auditStatus,
						@Param("createdDate") String createdDate,
						@Param("endDate") String endDate);



	
	@Query(nativeQuery = true, value = "SELECT ins.interfaceId, ins.fieldId, ins.fieldSystemId, ins.fieldName, ins.systemName, ins.interfaceName, ins.interfaceCode, ins.interfaceType," 
							+ " ins.auditStatus, ins.createdDate, ins.interfaceVersion, ins.interfaceDes, ins.interfaceStatus, ins.bundleName, ins.bundleType"
							+ " FROM (SELECT th.if_http_id interfaceId, tfd.field_id fieldId, ts.field_system_id fieldSystemId, tfd.field_name fieldName,"
							+ " ts.system_name systemName, th.if_name interfaceName, th.if_code interfaceCode, th.if_type interfaceType," 
							+ " th.audit_status auditStatus, th.created_date createdDate, th.if_version interfaceVersion, th.if_desc interfaceDes, th.if_status interfaceStatus," 
							+ " (SELECT tb.BUNDLE_NAME FROM t_esb_bu_if_bundles_relation  thb, t_esb_bu_bundles tb WHERE (th.if_http_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.IS_DEFINED_BUNDLE_JAR = '1')"
							+ " OR (th.if_http_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.bundle_no =:bundleNo)) bundleName, th.bundle_type bundleType" 
							+ " FROM t_esb_bu_if_http th, t_cook_bu_field_system ts, t_cook_db_field tfd"
							+ " WHERE th.field_system_id = ts.field_system_id AND ts.field_id = tfd.field_id" 
							+ " AND EXISTS(SELECT tu.field_system_id FROM t_cook_bu_user_system tu WHERE th.field_system_id = tu.field_system_id AND tu.user_id = :userId)"
							+ " AND th.is_enable = '1')"
							+ " ins" 
							+ " WHERE (''=:fieldId OR ins.fieldId = :fieldId)"
							+ " AND (''=:fieldSystemId OR ins.fieldSystemId = :fieldSystemId)"
							+ " AND (''=:interfaceName OR ins.interfaceName like :interfaceName)"
							+ " AND (''=:interfaceCode OR ins.interfaceCode like :interfaceCode)"
							+ " AND (''=:interfaceStatus OR ins.interfaceStatus = :interfaceStatus)"
							+ " AND (''=:auditStatus OR ins.auditStatus = :auditStatus)"
							+ " AND (''=:createdDate OR DATE_FORMAT(ins.createdDate, '%Y-%m-%d %H:%i:%S') BETWEEN DATE_FORMAT(:createdDate, '%Y-%m-%d %H:%i:%S')"
							+ " AND DATE_FORMAT(:endDate, '%Y-%m-%d %H:%i:%S')) ORDER BY ins.createdDate DESC LIMIT :pageNumber,:pageSize")
	List<Object[]> findHttpInterfacesByUser(@Param("bundleNo") String bundleNo,
										@Param("userId") String userId,
										@Param("fieldId") String fieldId,
										@Param("fieldSystemId") String fieldSystemId,
										@Param("interfaceName") String interfaceName,
										@Param("interfaceCode") String interfaceCode,
										@Param("interfaceStatus") String interfaceStatus,
										@Param("auditStatus") String auditStatus,
										@Param("createdDate") String createdDate,
										@Param("endDate") String endDate,
										@Param("pageNumber") long pageNumber,
										@Param("pageSize") long pageSize);
	
	
	
	@Query(nativeQuery = true, value = "SELECT count(ins.interfaceId)"
			+ " FROM (SELECT tf.if_ftp_id interfaceId, tfd.field_id fieldId, ts.field_system_id fieldSystemId, tfd.field_name fieldName, "
			+ " ts.system_name systemName, tf.if_name interfaceName, tf.if_code interfaceCode, tf.if_type interfaceType," 
			+ " tf.audit_status auditStatus, tf.created_date createdDate, tf.if_version interfaceVersion, tf.if_desc interfaceDes, tf.if_status interfaceStatus," 
			+ " (SELECT tb.BUNDLE_NAME FROM t_esb_bu_if_bundles_relation  thb, t_esb_bu_bundles tb WHERE (tf.if_ftp_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.IS_DEFINED_BUNDLE_JAR = '1')"
							+ " OR (tf.if_ftp_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.bundle_no = :bundleNoFtp)) bundleName,'' bundleType" 
			+ " FROM t_esb_bu_if_ftp tf, t_cook_bu_field_system ts, t_cook_db_field tfd"
			+ " WHERE tf.field_system_id = ts.field_system_id AND ts.field_id = tfd.field_id" 
			+ " AND EXISTS(SELECT tu.field_system_id FROM t_cook_bu_user_system tu WHERE tf.field_system_id = tu.field_system_id AND tu.USER_ID = :userId)"
			+ " AND tf.is_enable = '1')"
			+ " ins" 
			+ " WHERE (''=:fieldId OR ins.fieldId = :fieldId)"
			+ " AND (''=:fieldSystemId OR ins.fieldSystemId = :fieldSystemId)"
			+ " AND (''=:interfaceName OR ins.interfaceName like :interfaceName)"
			+ " AND (''=:interfaceCode OR ins.interfaceCode like :interfaceCode)"
			+ " AND (''=:interfaceStatus OR ins.interfaceStatus = :interfaceStatus)"
			+ " AND (''=:auditStatus OR ins.auditStatus = :auditStatus)"
			+ " AND (''=:createdDate OR DATE_FORMAT(ins.createdDate, '%Y-%m-%d %H:%i:%S') BETWEEN DATE_FORMAT(:createdDate, '%Y-%m-%d %H:%i:%S')"
			+ " AND DATE_FORMAT(:endDate, '%Y-%m-%d %H:%i:%S'))")
	List<Object> countFtpInterfacesByUser(@Param("bundleNoFtp") String bundleNoFtp,
						@Param("userId") String userId,
						@Param("fieldId") String fieldId,
						@Param("fieldSystemId") String fieldSystemId,
						@Param("interfaceName") String interfaceName,
						@Param("interfaceCode") String interfaceCode,
						@Param("interfaceStatus") String interfaceStatus,
						@Param("auditStatus") String auditStatus,
						@Param("createdDate") String createdDate,
						@Param("endDate") String endDate);



	
	@Query(nativeQuery = true, value = "SELECT ins.interfaceId, ins.fieldId, ins.fieldSystemId, ins.fieldName, ins.systemName, ins.interfaceName, ins.interfaceCode, ins.interfaceType," 
							+ " ins.auditStatus, ins.createdDate, ins.interfaceVersion, ins.interfaceDes, ins.interfaceStatus, ins.bundleName, ins.bundleType"
							+ " FROM (SELECT tf.if_ftp_id interfaceId, tfd.field_id fieldId, ts.field_system_id fieldSystemId, tfd.field_name fieldName, "
							+ " ts.system_name systemName, tf.if_name interfaceName, tf.if_code interfaceCode, tf.if_type interfaceType," 
							+ " tf.audit_status auditStatus, tf.created_date createdDate, tf.if_version interfaceVersion, tf.if_desc interfaceDes, tf.if_status interfaceStatus," 
							+ " (SELECT tb.BUNDLE_NAME FROM t_esb_bu_if_bundles_relation  thb, t_esb_bu_bundles tb WHERE (tf.if_ftp_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.IS_DEFINED_BUNDLE_JAR = '1') "
											+ " OR (tf.if_ftp_id = thb.if_id AND tb.bundle_id = thb.bundle_id AND tb.bundle_no = :bundleNoFtp)) bundleName,' ' bundleType"
							+ " FROM t_esb_bu_if_ftp tf, t_cook_bu_field_system ts, t_cook_db_field tfd"
							+ " WHERE tf.field_system_id = ts.field_system_id AND ts.field_id = tfd.field_id" 
							+ " AND EXISTS(SELECT tu.field_system_id FROM t_cook_bu_user_system tu WHERE tf.field_system_id = tu.field_system_id AND tu.USER_ID = :userId)"
							+ " AND tf.is_enable = '1')"
							+ " ins" 
							+ " WHERE (''=:fieldId OR ins.fieldId = :fieldId)"
							+ " AND (''=:fieldSystemId OR ins.fieldSystemId = :fieldSystemId)"
							+ " AND (''=:interfaceName OR ins.interfaceName like :interfaceName)"
							+ " AND (''=:interfaceCode OR ins.interfaceCode like :interfaceCode)"
							+ " AND (''=:interfaceStatus OR ins.interfaceStatus = :interfaceStatus)"
							+ " AND (''=:auditStatus OR ins.auditStatus = :auditStatus)"
							+ " AND (''=:createdDate OR DATE_FORMAT(ins.createdDate, '%Y-%m-%d %H:%i:%S') BETWEEN DATE_FORMAT(:createdDate, '%Y-%m-%d %H:%i:%S')"
							+ " AND DATE_FORMAT(:endDate, '%Y-%m-%d %H:%i:%S')) ORDER BY ins.createdDate DESC LIMIT :pageNumber,:pageSize")
	List<Object[]> findFtpInterfacesByUser(@Param("bundleNoFtp") String bundleNoFtp,
										@Param("userId") String userId,
										@Param("fieldId") String fieldId,
										@Param("fieldSystemId") String fieldSystemId,
										@Param("interfaceName") String interfaceName,
										@Param("interfaceCode") String interfaceCode,
										@Param("interfaceStatus") String interfaceStatus,
										@Param("auditStatus") String auditStatus,
										@Param("createdDate") String createdDate,
										@Param("endDate") String endDate,
										@Param("pageNumber") long pageNumber,
										@Param("pageSize") long pageSize);

}
